import pymysql

host = 'localhost'
user = 'root'
passwd = '123456'
db = 'spider'


def salaryJob():
    conn = pymysql.connect(host=host,
                           user=user,
                           passwd=passwd,
                           db=db,
                           charset="utf8")
    cur = conn.cursor()
    sql = 'select Job,CAST(MaxPay as CHAR) as MaxPay from recruitment order by recruitment.MaxPay desc limit 10'
    cur.execute(sql)
    conn.close()
    results = cur.fetchall()
    results_dict = dict(results)
    return results_dict


def MinSalary():
    conn = pymysql.connect(host=host,
                           user=user,
                           passwd=passwd,
                           db=db,
                           charset="utf8")
    cur = conn.cursor()
    sql = 'select Job, CAST(MaxPay as CHAR) as MaxPay, CAST(MinPay as CHAR) as MinPay from recruitment order by recruitment.MaxPay desc limit 10'
    cur.execute(sql)
    conn.close()
    results = cur.fetchall()
    results_dict = {}
    for i in results:
        max = i[1]
        min = i[2]
        results_list = [max, min]
        results_dict[i[0]] = results_list
    return results_dict

def salaryEducation():
    conn = pymysql.connect(host=host,
                           user=user,
                           passwd=passwd,
                           db=db,
                           charset="utf8")
    cur = conn.cursor()
    sql = 'select Experience,CAST(MaxPay as CHAR) as MaxPay from recruitment order by recruitment.MaxPay'
    cur.execute(sql)
    conn.close()
    results = cur.fetchall()
    results_dict = dict(results)
    return results_dict

def numEducation():
    conn = pymysql.connect(host=host,
                           user=user,
                           passwd=passwd,
                           db=db,
                           charset="utf8")
    cur = conn.cursor()
    sql = 'SELECT Education, COUNT(`Education`) from recruitment GROUP BY recruitment.Education '
    cur.execute(sql)
    conn.close()
    results = cur.fetchall()
    results_dict = dict(results)
    return results_dict

# MinSalary()
# salaryJob()
salaryEducation()
numEducation()

# sql = 'select Job,CAST(MaxPay as CHAR) as MaxPay from recruitment order by recruitment.MaxPay desc limit 10'
# cur.execute(sql)
# results = cur.fetchall()
# results_dict = dict(results)
# for i in results_dict:
#     print(results_dict.keys())
# # print(results)
